<?php
/**********************************************************************************
WIKINDX: Bibliographic Management system.
Copyright (C)

This program is free software; you can redistribute it and/or modify it under the terms 
of the GNU General Public License as published by the Free Software Foundation; either 
version 2 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; 
without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 
See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program; 
if not, write to the 
Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

The WIKINDX Team 2007
sirfragalot@users.sourceforge.net
**********************************************************************************/

/*
* SQL abstraction layer for using MySQL.
* This class is provided where the PHP version is problematic with ADOdb and its methods emulate those found in 
* ADODBSQL.php.
* NB As of v3.1.1, adoDB is used solely to upgrade the database structure.
*
* CVS keywords
* @author $author$
* @version $version$
*/
class SQL
{
	
	//object last record set (query result)
	var $recordset;
	
	/*
	* Constructor
	*
	* @author Mark Grimshaw
	* @version 2
	* @param none
	* @return boolean
	*/
	function SQL(){
		include("config.php");
		include_once("core/messages/ERRORS.php");
		$this->errors = new ERRORS();
		include_once("core/messages/UTF8.php");
		$this->utf8 = new UTF8();
		$this->handle = NULL;
		if(!isset($WIKINDX_DB_HOST) || !isset($WIKINDX_DB_USER) || !isset($WIKINDX_DB_PASSWORD) 
			|| !isset($WIKINDX_DB) || !isset($WIKINDX_DB_TYPE) || !isset($WIKINDX_DB_PERSISTENT))
			die($this->errors->text("dbError", "config"));
		$this->host = $WIKINDX_DB_HOST;
		$this->user = $WIKINDX_DB_USER;
		$this->pword = $WIKINDX_DB_PASSWORD;
		$this->dbase = $WIKINDX_DB;
		$this->dbaseType = $WIKINDX_DB_TYPE;
		$this->dbPersistent = $WIKINDX_DB_PERSISTENT;
		$this->escapeString = FALSE;
		if(!$this->open()) die($this->errors->text("dbError", "open"));
		$this->mysqlVersion = mysql_get_server_info();
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param optional XML file (used for extra tables used in plug-in modules)
	*/
	function createMySQL($xmlFile = FALSE)
	{
// Close database handle opened by PHP as simultaneous database connections can be problematic with database persistence.
		mysql_close($this->handle);
		include_once("core/sql/adodb.inc.php");
		include_once("core/sql/adodb-xmlschema.inc.php");
		$db =& NewADOConnection($this->dbaseType);
		$db->Connect($this->host, $this->user, $this->pword, $this->dbase);
//$db->debug = true; 
		if($xmlFile)
			$schemaFile = $xmlFile;
		else
			$schemaFile = 'update/createSQL.xml';
		$schema = new adoSchema($db);
		$schema->ContinueOnError(TRUE);
//		$schema->ExecuteInline(TRUE);
		$sql = $schema->ParseSchema($schemaFile);
		$result = $schema->ExecuteSchema();
		$db->Close();
// Re-open database with PHP methods.
		if(!$this->open()) die($this->errors->text("dbError", "open"));
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param none
	* @return boolean
	*/
	function open(){
		if($this->dbPersistent === TRUE)
		{
			if(!$this->handle = mysql_pconnect($this->host, $this->user, $this->pword))
				return FALSE;
		}
		else
		{
			if(!$this->handle = mysql_connect($this->host, $this->user, $this->pword))
				return FALSE;
		}
		if(!mysql_select_db($this->dbase, $this->handle))
			return FALSE;
		return TRUE;
	}
	
	/*
	* execute queries
	*
	* @author Mark Grimshaw
	* @version 1
	* @param string query
	* @return object
	*/
	function query($querystring)
	{
//print "$querystring<P>";
		$this->error = $this->errors->text("dbError", "read") . "<P>" . $querystring;
		if(!$recordset = mysql_query($querystring, $this->handle))
			die($this->error);
		global $WIKINDX_DB_QUERIES;
		$WIKINDX_DB_QUERIES++;
		return $recordset;
	}
	
	/*
	* execute queries - ignore error warnings (used with SYSTEMCHECK.php)
	*
	* @author Mark Grimshaw
	* @version 1
	* @param string query
	* @return object
	*/
	function queryNoError($querystring){
		$recordset = mysql_query($querystring, $this->handle);
		global $WIKINDX_DB_QUERIES;
		$WIKINDX_DB_QUERIES++;
		return $recordset;
	}
	
	/*
	* return numRows from recordset
	*
	* @author Mark Grimshaw
	* @version 1
	* @param object recordset
	* @return int
	*/
	function numRows($recordset){
		return mysql_num_rows($recordset);
	}

	/*
	* @author Mark Grimshaw
	* Actually the same as fetchRow() below but here for compatibility purposes.
	* @version 1
	* @param object
	* @return array
	*/
	function loopRecordSet($recordset)
	{
		$row = mysql_fetch_assoc($recordset);
		if(!is_array($row))
			return $row;
//		$this->getCollectionString(&$row);
		$row = array_map(array($this, "sqlAddSlashes"), $row);
		return $row;
	}
	
	/*
	* @author Mark Grimshaw
	* @version 2
	* @param array
	* @return array
	*/
	function fetchRow($recordset)
	{
		$row = mysql_fetch_assoc($recordset);
		if(!is_array($row))
			return $row;
//		$this->getCollectionString(&$row);
		$row = array_map(array($this, "sqlAddSlashes"), $row);
		return $row;
	}
// Details of collections are stored centrally
	function getCollectionString(&$row)
	{
		if(array_key_exists('collectionString', $row) && $row['collectionString'])
		{
			foreach(unserialize(base64_decode($row['collectionString'])) as $key => $value)
			{
				$row[$key] = $value;
				if($key == 'publisher')
				{
					$recordset = $this->select(array('WKX_publisher'), 
						array("publisherName", "publisherLocation", "publisherType"), 
						' WHERE ' . $this->formatField('id') . '=' . $this->tidyInput($row['publisher']));
					$row2 = mysql_fetch_assoc($recordset);
					if(is_array($row2))
					{
						$row2 = array_map(array($this, "sqlAddSlashes"), $row2);
						foreach($row2 as $key2 => $value2)
							$row[$key2] = $value2;
					}
				}
			}
		}
	}
	function sqlAddSlashes($element)
	{
		return addslashes($element);
	}
	/*
	* @author Mark Grimshaw
	* @version 2
	* @param array
	* @return string html
	*/
	function fetchOne($recordset){
		$row = mysql_fetch_row($recordset);
		return addslashes($row[0]);
	}
	
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param array tables
	* @param array fields
	* @param recordset
	*/
	function select($tables, $fields, $conditions = ''){
		$field = is_array($fields) ? $this->formatFields($fields) : $fields;
		$table = $this->formatTables($tables);
		$this->error = $this->errors->text("dbError", "read");
		return $this->query("SELECT $field FROM $table $conditions");
	}
	
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param array tables
	* @param array fields
	* @param recordset
	*/
	function selectMax($table, $maxField, $conditions = '')
	{
		$table = $this->formatTables($table);
		$this->error = $this->errors->text("dbError", "read");
		return $this->query("SELECT MAX($maxField) AS $maxField FROM $table $conditions");
	}
	
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param array tables
	* @param array fields
	* @param recordset
	*/
	function selectMin($table, $minField, $conditions = '')
	{
		$table = $this->formatTables($table);
		$this->error = $this->errors->text("dbError", "read");
		return $this->query("SELECT MIN($minField) AS $minField FROM $table $conditions");
	}
	
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param array tables
	* @param array fields
	* @param recordset
	*/
	function selectAverageDate($table, $field, $conditions = '')
	{
		$table = $this->formatTables($table);
		$this->error = $this->errors->text("dbError", "read");
		return $this->query("SELECT FROM_UNIXTIME(AVG(UNIX_TIMESTAMP($field))) 
			AS $field FROM $table $conditions");
	}
	
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param array tables
	* @param array fields
	* @param recordset
	*/
	function selectCount($table, $field, $conditions = '')
	{
		$table = $this->formatTables($table);
		$this->error = $this->errors->text("dbError", "read");
		return $this->query("SELECT COUNT(*) AS count, $field FROM $table $conditions GROUP BY $field");
	}
	
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param array tables
	* @param array fields
	* @param recordset
	*/
	function selectCountMax($table, $field, $ascDesc = 'DESC', $conditions = '')
	{
		$table = $this->formatTables($table);
		$this->error = $this->errors->text("dbError", "read");
		return $this->query("SELECT COUNT(*) AS count, $field FROM $table $conditions GROUP BY $field 
			ORDER BY count $ascDesc");
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param array tables
	* @param array fields
	* @param recordset
	*/
	function selectCounts($table, $field, $order = 'ASC')
	{
// NB NULL value rows are not gathered
		$table = $this->formatTables($table);
		$this->error = $this->errors->text("dbError", "read");
		return $this->query("SELECT `$field`, COUNT(`$field`) AS count, `$field` FROM $table 
			GROUP BY `$field` ORDER BY count $order");
	}
	
	/*
	* @author Mark Grimshaw
	* @version 1
	* @see SQL::select()
	* @param array tables
	* @param array field
	* @param recordset
	*/
	function selectWithExceptions($tables, $fields, $conditions = '')
	{
		if(is_array($fields))
			$field = $this->formatFields($fields, TRUE);
		else
			$field = $fields;
		$table = $this->formatTables($tables);
		$this->error = $this->errors->text("dbError", "read");
		return $this->query("SELECT $field FROM $table $conditions");
	}
	
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param array tables
	* @param array field
	* @param SQL SELECT string
	*/
	function selectNoExecute($tables, $fields, $conditions = ''){
	
		$field = is_array($fields) ? $this->formatFields($fields) : $fields;
		$table = $this->formatTables($tables);
		$this->error = $this->errors->text("dbError", "read");
		return "SELECT $field FROM $table $conditions";
	}
	
	/*
	* @author Mark Grimshaw
	* @version 1
	* @see SQL::select()
	* @param array tables
	* @param array field
	* @param recordset
	*/
	function selectNoExecuteWithExceptions($tables, $fields, $conditions = '')
	{
		if(is_array($fields))
			$field = $this->formatFields($fields, TRUE);
		else
			$field = $fields;
		$table = $this->formatTables($tables);
		$this->error = $this->errors->text("dbError", "read");
		return "SELECT $field FROM $table $conditions";
	}
	
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param array tables
	* @param array field
	* @param recordset
	*/
	function limit($sql, $limit, $offset){
		$this->error = $this->errors->text("dbError", "read");
		return $this->query($sql . " LIMIT " . $offset . ", " . $limit);
	}
	
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param string table
	* @param array fields
	* @param array values
	* @param string conditions
	*/
	function insert($table, $fields, $values, $conditions = '')
	{
		$field = is_array($fields) ? $this->formatFields($fields) : "`$fields`";
		$table = "`" . $table . "`";
		$value = is_array($values) ? $this->formatValues($values) : $this->tidyInput($values);
		$this->error = $this->errors->text("dbError", "write");
		$this->query("INSERT INTO $table ($field) VALUES($value) $conditions");
	}
	
	/*
	* @author Mark Grimshaw
	* @version 1
	* @see SQL::update()
	* @param string table
	* @param array updateArray
	* @param string conditions
	*/
	function update($table, $updateArray, $conditions = ''){
		$set = $this->formatUpdate($updateArray);
		$this->error = $this->errors->text("dbError", "write");
		$this->query("UPDATE `$table` $set $conditions");
	}
	
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param string table
	* @param string set statement
	* @param string conditions
	*/
	function updateSingle($table, $set, $conditions = ''){
		$this->error = $this->errors->text("dbError", "write");
		$this->query("UPDATE `$table` SET $set $conditions");
	}
	
	/*
	* @author Mark Grimshaw
	* @version 1
	* @see SQL::updateNull()
	* @param string table
	* @param array fields to set to null
	* @param string conditions
	*/
	function updateNull($table, $nulls, $conditions = ''){
		$sql = "UPDATE `$table` SET ";
		foreach($nulls as $null)
			$sqlArray[] = "`$null`=NULL";
		$this->error = $this->errors->text("dbError", "write");
		$this->query($sql . implode(", ", $sqlArray) . " $conditions");	
	}

	/*
	* @author Mark Grimshaw
	* @version 1
	* @see SQL::listFields()
	* @param string table
	*/
	function listFields($table){
		return $this->query("SHOW COLUMNS FROM `$table`");
	}
	
	/*
	* show all tables in db
	* NB Required to return an array as the no. of elements in that array is checked in init/SYSTEMCHECK
	*
	* @author Mark Grimshaw
	* @version 2
	* @param none
	* @return array
	*/
	function listTables(){
		$tables = array();
		$recordset = $this->query("SHOW TABLES FROM `$this->dbase`");
		while($row = $this->loopRecordSet($recordset))
			$tables[] = $row["Tables_in_" . $this->dbase];
		return $tables;
	}
	
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param string table
	* @param conditions
	*/
	function delete($table, $conditions = ''){
		$this->error = $this->errors->text("dbError", "write");
		$this->query("DELETE FROM `$table` $conditions");
	}
	
	/*
	* return last auto_increment ID
	*
	* @author Mark Grimshaw
	* @version 1
	* @param none
	* @return int/false
	*/
	function lastAutoID(){
		return mysql_insert_id($this->handle);
	}
	
	// format a single field usually for use in a CONDITION clause
	function formatField($string)
	{
		return "`" . str_replace(".", "`.`", $string) . "`";
	}
	
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param array fields
	* @return string
	*/
	function formatFields($fields, $withExceptions = FALSE){

		foreach($fields as $field)
		{
			if(is_array($field))
			{
				if($withExceptions)
					$array[] = $this->formatAliasWithExceptions($field);
				else
					$array[] = $this->formatAlias($field);
			}
			else
				$array[] = "`" . str_replace(".", "`.`", trim($field)) . "`";
		}
		return implode(", ", $array);
	}
	
	/*
	*
	*/
	function formatTables($tables){

		foreach($tables as $table)
		{
			if(is_array($table))
				$array[] = $this->formatAlias($table);
			else
				$array[] = "`" . trim($table) . "`";
		}
// NB http://bugs.mysql.com/bug.php?id=13551
		if($this->mysqlVersion{0} == 3)
			return implode(", ", $array);
// MySQL 4.x and above?
		return '(' . implode(", ", $array) . ')';
	}
	
	/*
	*
	*/
	function formatTable($table)
	{
		if($this->mysqlVersion{0} == 3)
			return trim($table);
// MySQL 4.x and above?
		return "(`" . trim($table) . "`)";
	}
	
	/*
	*
	*/
	function formatValues($values){

		foreach($values as $value)
		{
			if(is_array($value))
				$array[] = $this->formatAlias($value);
			else
				$array[] = $this->tidyInput($value);
		}
		return implode(", ", $array);
	}
	
	/*
	* @author Mark Grimshaw
	* @version 1
	* @see ADOSQL::update
	* @param array fields
	* @return string
	*/
	function formatUpdate($array){
		foreach($array as $field => $value)
			$fieldArray[] = "`" . str_replace(".", "`.`", $field) . "`=" . $this->tidyInput($value);
		return "SET " . implode(", ", $fieldArray);
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param array fields
	* @return string
	*/
	function formatAlias($array)
	{
		list($key, $value) = each($array);
		return "`" . str_replace(".", "`.`", $key) . "` AS `$value`";
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param array fields
	* @return string
	*/
	function formatAliasWithExceptions($array)
	{
		list($key, $value) = each($array);
/**
* For something like DATE_FORMAT(timestamp,'%d/%b/%Y'), we don't want backticks.
* Add other exceptions here...
*/
		if(preg_match("/^DATE_FORMAT/", $key))
			return $key . " AS $value";
		if(preg_match("/^UNIX_TIMESTAMP/", $key))
			return $key . " AS $value";
		return $key . " AS $value";
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param string $UNIX epoch time
	*/
	function formatTimestamp($time = FALSE)
	{
		if(!$time)
			$time = time();
		return date("Y-m-d H:i:s", $time);
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param string $table
	*/
	function optimize($table)
	{
		$this->query("OPTIMIZE TABLE `$table`");
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param string $string
	* @return string
	*
	* Encode all database input to UTF-8
	*/
	function tidyInput($string)
	{
		if($this->escapeString)
			return "'" . $this->utf8->smartUtf8_encode(mysql_real_escape_string(trim($string))) . "'";
		else
			return "'" . $this->utf8->smartUtf8_encode(trim($string)) . "'";
	}
// tidyInput() crashes when used for writeCache()
	function tidyInputNoUtf8($string)
	{
		if($this->escapeString)
			return "'" . mysql_real_escape_string(trim($string)) . "'";
		else
			return "'" . trim($string) . "'";
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param string $string
	*/
	function tidyInputNoTrim($string)
	{
		if($this->escapeString)
			return "'" . mysql_real_escape_string($string) . "'";
		else
			return "'" . $string . "'";
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param string $string
	*
	* For use with ORDER or GROUP by clauses
	*/
	function tidyInputClause($string)
	{
		if($this->escapeString)
			return "`" . str_replace(".", "`.`", mysql_real_escape_string(trim($string))) . "`";
		else
			return "`" . str_replace(".", "`.`", trim($string)) . "`";
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param string $separator
	* @param array $array
	*/
	function concat($array, $separator = FALSE)
	{
		if($this->dbaseType == 'mysql')
		{
			if($separator !== FALSE)
				return "CONCAT_WS('$separator', " . implode(", ", $array) . ")";
			else
				return "CONCAT(" . implode(", ", $array) . ")";
		}
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param string $field
	* @param array $array
	*/
	function writeCache($field, $array)
	{
		$this->updateSingle('WKX_cache', 
			$this->formatField($field) ."=" . $this->tidyInputNoUtf8(base64_encode(serialize($array))));
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param string $field
	*/
	function readCache($field)
	{
		$resultset = $this->select(array('WKX_cache'), array($field));
		if(!$this->numRows($resultset))
			return FALSE;
		return unserialize(base64_decode($this->fetchOne($resultset)));
	}
	/*
	* @author Mark Grimshaw
	* @version 1
	* @param string $field
	*/
	function deleteCache($field)
	{
		$this->updateNull('WKX_cache', array($field));
	}
}

?>
